﻿#region Using

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Runtime.InteropServices;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

#endregion

namespace Microsoft.SqlServer.Mds
{
    [DtsPipelineComponent(DisplayName = "SQL Server MDS Destination", ComponentType = ComponentType.DestinationAdapter)]
    public class MdsDestination : PipelineComponent
    {
        #region Members

        private State state = new State();

        #endregion

        #region Constants and Enums

        //Component custom properties
        private const string INPUT_NAME = "Component Input";        //Main input for component
        private const string ERROROUTPUT_NAME = "Error Output";     //Error output for component
        private const string CONNECTION_NAME = "Mds Connection";    //Used to identify the database connection
        private const string MODEL_NAME = "Model Name";             //Key for custom property (Model)
        private const string VERSION_NAME = "Version Name";         //Key for custom property (Version)
        private const string ENTITY_NAME = "Entity Name";           //Key for custom property (Entity)
        private const string MEMBERTYPE_NAME = "Member Type";       //Key for custom property (MemberType)
        private const string HIERARCHY_NAME = "Hierarchy Name";     //Key for custom property (Hierarchy)
        private const string BATCH_NAME = "Batch Name";             //Key for custom property (Batch)
        private const string USER_NAME = "Mds Username";            //Key for custom property (Username)

        private enum MemberType
        {
            EN = 1,
            HP = 2
        }

        #endregion private constants

        #region Design Time

        #region ProvideComponentProperties

        public override void ProvideComponentProperties()
        {
            base.ProvideComponentProperties();
            this.RemoveAllInputsOutputsAndCustomProperties();

            this.ComponentMetaData.Name = Localized.ComponentName;
            this.ComponentMetaData.Description = Localized.ComponentDescription;
            string version = GetComponentVersion().ToString(CultureInfo.InvariantCulture);
            this.ComponentMetaData.ContactInfo = Localized.ComponentContact + " \u00A9 " + version;

            this.GetRuntimeConnection();
            this.AddComponentInputs();
            this.AddErrorRowDisposition();
            this.EnableExternalMetadata();
            this.AddCustomProperties();
        }

        private Int32 GetComponentVersion()
        {
            var attr = Attribute.GetCustomAttribute(this.GetType(), typeof(DtsPipelineComponentAttribute), false);
            DtsPipelineComponentAttribute dtsattr = (DtsPipelineComponentAttribute)attr;
            return dtsattr.CurrentVersion;
        }

        private void GetRuntimeConnection()
        {
            IDTSRuntimeConnection100 sqlConn;
            try
            {
                sqlConn = this.ComponentMetaData.RuntimeConnectionCollection[CONNECTION_NAME];
            }
            catch (Exception)
            {
                sqlConn = this.ComponentMetaData.RuntimeConnectionCollection.New();
                sqlConn.Name = CONNECTION_NAME;
                sqlConn.Description = Localized.ConnectionDescription;
            }
        }

        private void AddComponentInputs()
        {
            IDTSInput100 input = this.ComponentMetaData.InputCollection.New();
            input.Name = INPUT_NAME;
            input.Description = Localized.InputDescription;
            input.HasSideEffects = true;
        }

        private void AddErrorRowDisposition()
        {
            IDTSInput100 input = this.ComponentMetaData.InputCollection[INPUT_NAME];
            input.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;

            IDTSOutput100 error = this.ComponentMetaData.OutputCollection.New();
            error.Name = ERROROUTPUT_NAME;
            error.Description = Localized.ErrorOutputDescription;
            error.IsErrorOut = true;
            error.SynchronousInputID = input.ID;
            error.ExclusionGroup = 1;
            ComponentMetaData.UsesDispositions = true;
        }

        private void EnableExternalMetadata()
        {
            IDTSInput100 input = this.ComponentMetaData.InputCollection[INPUT_NAME];
            input.ExternalMetadataColumnCollection.IsUsed = true;
            this.ComponentMetaData.ValidateExternalMetadata = true;
            this.ComponentMetaData.Version = GetComponentVersion();
        }

        private void AddCustomProperties()
        {
            //Use a local delegate for modularity
            Func<string, string, IDTSCustomProperty100> addProperty = delegate(string name, string description)
            {
                IDTSCustomProperty100 property = this.ComponentMetaData.CustomPropertyCollection.New();
                property.Name = name;
                property.Description = description;
                property.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;

                return property;
            };

            //Add the custom properties by calling the local delegate
            addProperty(MODEL_NAME, Localized.PropertyModelDescription).Value = string.Empty;
            addProperty(VERSION_NAME, Localized.PropertyVersionDescription).Value = string.Empty;
            addProperty(ENTITY_NAME, Localized.PropertyEntityDescription).Value = string.Empty;
            addProperty(HIERARCHY_NAME, Localized.PropertyHierarchyDescription).Value = string.Empty;
            addProperty(BATCH_NAME, Localized.PropertyBatchDescription).Value = string.Empty;
            addProperty(USER_NAME, Localized.PropertyUserNameDescription).Value = string.Empty;

            //Treat member type specially, since we want an enum dropdown in the UI
            IDTSCustomProperty100 mt = addProperty(MEMBERTYPE_NAME, Localized.PropertyMemberTypeDescription);
            mt.TypeConverter = typeof(MemberType).AssemblyQualifiedName;
            mt.Value = MemberType.EN;
        }

        #endregion

        #region ReinitializeMetaData

        public override void ReinitializeMetaData()
        {
            base.ReinitializeMetaData();

            //If we are in a connected state, continue
            if (!state.IsConnected)
            {
                bool cancel;
                ErrorSupport.FireError(HResults.DTS_E_CONNECTIONREQUIREDFORMETADATA, out cancel);
                throw new PipelineComponentHResultException(HResults.DTS_E_CONNECTIONREQUIREDFORMETADATA);
            }

            //Remove all input columns and external metadata columns
            IDTSInput100 input = this.ComponentMetaData.InputCollection[INPUT_NAME];
            input.ExternalMetadataColumnCollection.RemoveAll();
            input.InputColumnCollection.RemoveAll();

            //Generate the external columns
            this.GenerateExternalColumns();
        }

        private void GenerateExternalColumns()
        {
            string tablename = this.GetMemberTableName();

            if (!string.IsNullOrEmpty(tablename))
            {
                using (DataTable metadata = this.GetMemberTableMetadata(tablename))
                {
                    if (metadata != null)
                    {
                        IDTSInput100 input = this.ComponentMetaData.InputCollection[INPUT_NAME];
                        IDTSExternalMetadataColumnCollection100 ecc = input.ExternalMetadataColumnCollection;

                        //Get the column ordinals from the metadata table
                        int columnNameOrdinal = metadata.Columns.IndexOf("ColumnName");
                        int dataTypeOrdinal = metadata.Columns.IndexOf("DataType");
                        int columnSizeOrdinal = metadata.Columns.IndexOf("ColumnSize");
                        int numericPrecisionOrdinal = metadata.Columns.IndexOf("NumericPrecision");
                        int numericScaleOrdinal = metadata.Columns.IndexOf("NumericScale");

                        //Grab the friendly names mappings from mdm.tblAttribute
                        SqlCommand cmd = state.MdsConnection.CreateCommand();
                        cmd.CommandText = @"
                            SELECT a.TableColumn, a.[Name] AS FriendlyName, CASE WHEN a.DomainEntity_ID IS NULL THEN 0 ELSE 1 END AS IsDba
                            FROM mdm.tblAttribute AS a 
                            INNER JOIN mdm.tblEntity AS e ON (a.Entity_ID = e.ID)
                            INNER JOIN mdm.tblModel AS m ON (e.Model_ID = m.ID)
                            WHERE m.[Name] = @model 
                            AND e.[Name] = @entity
                            AND a.[AttributeType_ID] IN (1, 2) --Freeform(1), DBA(2) (disallow FBA(4))
                            AND a.MemberType_ID = @membertype
                            AND (a.IsSystem = 0 OR a.IsCode = 1 OR a.IsName = 1);";
                        cmd.Parameters.AddWithValue("model", (string)ComponentMetaData.CustomPropertyCollection[MODEL_NAME].Value);
                        cmd.Parameters.AddWithValue("entity", (string)ComponentMetaData.CustomPropertyCollection[ENTITY_NAME].Value);
                        MemberType mt = (MemberType)ComponentMetaData.CustomPropertyCollection[MEMBERTYPE_NAME].Value;
                        cmd.Parameters.AddWithValue("membertype", (byte)mt);

                        //Map the friendly name to each attribute
                        using (SqlDataReader dr2 = cmd.ExecuteReader())
                        {
                            while (dr2.Read())
                            {
                                string tableColumn = (string)dr2[0];    //[TableColumn]
                                string friendlyName = (string)dr2[1];   //[FriendlyName]
                                bool isDba = (int)dr2[2] == 1;          //[IsDba]

                                foreach (DataRow row in metadata.Rows)
                                {
                                    if ((string)row[columnNameOrdinal] == tableColumn)
                                    {
                                        IDTSExternalMetadataColumn100 col = ecc.New();
                                        col.Name = friendlyName;
                                        col.CodePage = 0;

                                        //Convert from .Net type to DTS type
                                        Type type = (Type)row[dataTypeOrdinal];
                                        try
                                        {
                                            col.DataType = PipelineComponent.DataRecordTypeToBufferType(type);
                                        }
                                        catch (Exception)
                                        {
                                            bool cancel;
                                            ErrorSupport.FireErrorWithArgs(HResults.DTS_E_ADODESTDATATYPENOTSUPPORTED, out cancel, type.ToString(), col.Name, ComponentMetaData.IdentificationString);
                                            throw new PipelineComponentHResultException(HResults.DTS_E_ADODESTDATATYPENOTSUPPORTED);
                                        }

                                        //We masquerade DBA columns as if they were defined as a [Code] column, NVARCHAR(250)
                                        if (isDba)
                                        {
                                            col.DataType = DataType.DT_WSTR; //Instead of DT_I4
                                            col.Length = 250;
                                        }
                                        else if (col.DataType == DataType.DT_WSTR || col.DataType == DataType.DT_BYTES)
                                        {
                                            col.Length = (int)row[columnSizeOrdinal];
                                        }
                                        else if (col.DataType == DataType.DT_NUMERIC || col.DataType == DataType.DT_DECIMAL)
                                        {
                                            col.Precision = row.IsNull(numericPrecisionOrdinal) ? 0 : (short)row[numericPrecisionOrdinal];
                                            col.Scale = row.IsNull(numericScaleOrdinal) ? 0 : (short)row[numericScaleOrdinal];
                                        }

                                        break;
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        private string GetMemberTableName()
        {
            MemberType mt = (MemberType)ComponentMetaData.CustomPropertyCollection[MEMBERTYPE_NAME].Value;
            string column = (mt == MemberType.EN) ? "EntityTable" : "HierarchyParentTable";

            using (SqlCommand cmd = state.MdsConnection.CreateCommand())
            {
                cmd.CommandText = string.Format(CultureInfo.InvariantCulture, @"
                    SELECT e.[{0}]
                    FROM mdm.tblModel AS m
                    INNER JOIN mdm.tblEntity AS e ON (m.ID = e.Model_ID)
                    WHERE m.[Name] = @model AND e.[Name] = @entity;", column);
                cmd.Parameters.AddWithValue("model", (string)ComponentMetaData.CustomPropertyCollection[MODEL_NAME].Value);
                cmd.Parameters.AddWithValue("entity", (string)ComponentMetaData.CustomPropertyCollection[ENTITY_NAME].Value);

                string tablename = (string)cmd.ExecuteScalar();
                return tablename;
            }
        }

        private DataTable GetMemberTableMetadata(string tablename)
        {
            DataTable metadata = null;

            if (!string.IsNullOrEmpty(tablename))
            {
                using (SqlCommand cmd = state.MdsConnection.CreateCommand())
                {
                    cmd.CommandText = @"SELECT TOP 0 * FROM mdm.[" + tablename + "];";
                    using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
                    {
                        metadata = dr.GetSchemaTable();
                    }
                }
            }

            return metadata;
        }

        #endregion

        #endregion

        #region Run Time

        #region Connect

        public override void AcquireConnections(object transaction)
        {
            base.AcquireConnections(transaction);

            bool cancel;

            if (state.IsConnected)
            {
                ErrorSupport.FireError(HResults.DTS_E_ALREADYCONNECTED, out cancel);
                throw new PipelineComponentHResultException(HResults.DTS_E_ALREADYCONNECTED);
            }

            IDTSRuntimeConnection100 runtimeConn;
            try
            {
                runtimeConn = ComponentMetaData.RuntimeConnectionCollection[CONNECTION_NAME];
            }
            catch (Exception)
            {
                ErrorSupport.FireErrorWithArgs(HResults.DTS_E_CANNOTTFINDRUNTIMECONNECTIONOBJECT, out cancel, CONNECTION_NAME);
                throw new PipelineComponentHResultException(HResults.DTS_E_CANNOTTFINDRUNTIMECONNECTIONOBJECT);
            }

            IDTSConnectionManager100 connMgr = runtimeConn.ConnectionManager;
            if (connMgr == null)
            {
                ErrorSupport.FireErrorWithArgs(HResults.DTS_E_CONNECTIONMANANGERNOTASSIGNED, out cancel, CONNECTION_NAME);
                throw new PipelineComponentHResultException(HResults.DTS_E_CONNECTIONMANANGERNOTASSIGNED);
            }

            Object acquiredConnection;
            try
            {
                acquiredConnection = connMgr.AcquireConnection(transaction);
            }
            catch (Exception)
            {
                ErrorSupport.FireErrorWithArgs(HResults.DTS_E_ADODESTFAILEDTOACQUIRECONNECTION, out cancel, runtimeConn.ConnectionManagerID);
                throw new PipelineComponentHResultException(HResults.DTS_E_ADODESTFAILEDTOACQUIRECONNECTION);
            }

            state.MdsConnection = acquiredConnection as SqlConnection;
            if (state.MdsConnection == null)
            {
                ErrorSupport.FireErrorWithArgs(HResults.DTS_E_ADODESTNOTMANAGEDCONNECTION, out cancel, runtimeConn.ConnectionManagerID);
                DtsConvert.GetWrapper(connMgr).ReleaseConnection(acquiredConnection);
                throw new PipelineComponentHResultException(HResults.DTS_E_ADODESTNOTMANAGEDCONNECTION);
            }

            state.IsConnected = true;
        }

        public override void ReleaseConnections()
        {
            base.ReleaseConnections();

            if (state.MdsConnection != null)
            {
                IDTSRuntimeConnection100 conn = ComponentMetaData.RuntimeConnectionCollection[CONNECTION_NAME];
                IDTSConnectionManager100 mgr = conn.ConnectionManager;
                DtsConvert.GetWrapper(mgr).ReleaseConnection(state.MdsConnection);
            }

            state.MdsConnection = null;
            state.IsConnected = false;
        }

        #endregion

        #region Validate

        [CLSCompliant(false)]
        public override DTSValidationStatus Validate()
        {
            try
            {
                DTSValidationStatus status = base.Validate();
                if (status != DTSValidationStatus.VS_ISVALID)
                {
                    return status;
                }

                if ((status = ValidateConnection()) != DTSValidationStatus.VS_ISVALID)
                    return status;

                if ((status = ValidateInputs()) != DTSValidationStatus.VS_ISVALID)
                    return status;

                if ((status = ValidateOutputs()) != DTSValidationStatus.VS_ISVALID)
                    return status;

                if ((status = ValidateCustomProperties()) != DTSValidationStatus.VS_ISVALID)
                    return status;

                if ((status = ValidateExternalMetadataInformation()) != DTSValidationStatus.VS_ISVALID)
                    return status;

                return status;
            }
            catch (Exception)
            {
                return DTSValidationStatus.VS_ISCORRUPT;
            }
        }

        private DTSValidationStatus ValidateCustomProperties()
        {
            DTSValidationStatus status;

            if ((status = ValidateModelProperty()) != DTSValidationStatus.VS_ISVALID)
                return status;

            if ((status = ValidateVersionProperty()) != DTSValidationStatus.VS_ISVALID)
                return status;

            if ((status = ValidateEntityProperty()) != DTSValidationStatus.VS_ISVALID)
                return status;

            if ((status = ValidateMemberTypeProperty()) != DTSValidationStatus.VS_ISVALID)
                return status;

            if ((status = ValidateHierarchyProperty()) != DTSValidationStatus.VS_ISVALID)
                return status;

            if ((status = ValidateUsernameProperty()) != DTSValidationStatus.VS_ISVALID)
                return status;

            if ((status = ValidateBatchProperty()) != DTSValidationStatus.VS_ISVALID)
                return status;

            return status;
        }

        private DTSValidationStatus ValidateInputs()
        {
            bool cancel;

            //Component should have a single input
            if (this.ComponentMetaData.InputCollection.Count != 1)
            {
                ErrorSupport.FireErrorWithArgs(HResults.DTS_E_INCORRECTEXACTNUMBEROFINPUTS, out cancel, 1);
                return DTSValidationStatus.VS_ISCORRUPT;
            }

            //Check input has columns
            IDTSInput100 input = ComponentMetaData.InputCollection[INPUT_NAME];
            if (input.ExternalMetadataColumnCollection.Count == 0)
            {
                return DTSValidationStatus.VS_NEEDSNEWMETADATA;
            }

            //Check input columns are valid
            if (!this.ComponentMetaData.AreInputColumnsValid)
            {
                input.InputColumnCollection.RemoveAll();
                return DTSValidationStatus.VS_NEEDSNEWMETADATA;
            }

            //Input truncation disposition not supported
            if (input.TruncationRowDisposition != DTSRowDisposition.RD_NotUsed)
            {
                ErrorSupport.FireError(HResults.DTS_E_ADODESTINPUTTRUNDISPNOTSUPPORTED, out cancel);
                return DTSValidationStatus.VS_ISBROKEN;
            }

            //Check that column level disposition should not be set as it is not supported
            //Prevents customers setting the value programmatically
            IDTSInputColumnCollection100 icc = input.InputColumnCollection;
            for (int i = 0; i < icc.Count; i++)
            {
                if (icc[i].ErrorRowDisposition != DTSRowDisposition.RD_NotUsed)
                {
                    ErrorSupport.FireError(HResults.DTS_E_ADODESTCOLUMNERRORDISPNOTSUPPORTED, out cancel);
                    return DTSValidationStatus.VS_ISBROKEN;
                }

                if (icc[i].TruncationRowDisposition != DTSRowDisposition.RD_NotUsed)
                {
                    ErrorSupport.FireError(HResults.DTS_E_ADODESTCOLUMNTRUNDISPNOTSUPPORTED, out cancel);
                    return DTSValidationStatus.VS_ISBROKEN;
                }
            }

            return DTSValidationStatus.VS_ISVALID;
        }

        private DTSValidationStatus ValidateOutputs()
        {
            bool cancel;

            if (this.ComponentMetaData.OutputCollection.Count != 1)
            {
                ErrorSupport.FireErrorWithArgs(HResults.DTS_E_INCORRECTEXACTNUMBEROFOUTPUTS, out cancel, 1);
                return DTSValidationStatus.VS_ISCORRUPT;
            }

            IDTSInput100 input = this.ComponentMetaData.InputCollection[INPUT_NAME];
            IDTSOutput100 output = this.ComponentMetaData.OutputCollection[ERROROUTPUT_NAME];
            if (input.ErrorRowDisposition == DTSRowDisposition.RD_RedirectRow && !output.IsErrorOut)
            {
                //Does not have an error output 
                ErrorSupport.FireError(HResults.DTS_E_ADODESTNOERROROUTPUT, out cancel);
                return DTSValidationStatus.VS_ISCORRUPT;
            }

            return DTSValidationStatus.VS_ISVALID;
        }

        private DTSValidationStatus ValidateConnection()
        {
            if (this.ComponentMetaData.RuntimeConnectionCollection[CONNECTION_NAME].ConnectionManager == null)
            {
                FireEvent(EventType.Error, "No connection manager");
                return DTSValidationStatus.VS_ISBROKEN;
            }

            //Property is valid
            return DTSValidationStatus.VS_ISVALID;
        }

        private DTSValidationStatus ValidateModelProperty()
        {
            //Check for property existence
            IDTSCustomProperty100 prop = ComponentMetaData.CustomPropertyCollection[MODEL_NAME];

            if (prop == null)
            {
                throw new Exception("Custom property MODEL_NAME does not exist.");
            }

            //Check whether property value is NULL or empty
            string value = (prop.Value == null) ? null : ((string)prop.Value).Trim();
            if (string.IsNullOrEmpty(value))
            {
                FireEvent(EventType.Error, "The Model Name property must be set");
                return DTSValidationStatus.VS_ISBROKEN;
            }

            //Check whether property value is invalid
            SqlCommand cmd = state.MdsConnection.CreateCommand();
            cmd.CommandText = @"SELECT 1 FROM mdm.tblModel WHERE [Name] = @model";
            cmd.Parameters.AddWithValue("model", value);
            try
            {
                object o = cmd.ExecuteScalar();
                if (o == null || (int)o != 1) throw new Exception();
            }
            catch
            {
                FireEvent(EventType.Error, "The specified Model does not exist");
                return DTSValidationStatus.VS_ISBROKEN;
            }

            //Property is valid
            return DTSValidationStatus.VS_ISVALID;
        }

        private DTSValidationStatus ValidateVersionProperty()
        {
            //Check for property existence
            IDTSCustomProperty100 prop = ComponentMetaData.CustomPropertyCollection[VERSION_NAME];

            if (prop == null)
            {
                throw new Exception("Custom property VERSION_NAME does not exist.");
            }

            //Check whether property value is NULL or empty
            string value = (prop.Value == null) ? null : ((string)prop.Value).Trim();
            if (string.IsNullOrEmpty(value))
            {
                FireEvent(EventType.Error, "The Version Name property must be set");
                return DTSValidationStatus.VS_ISBROKEN;
            }

            //Check whether property value is invalid
            SqlCommand cmd = state.MdsConnection.CreateCommand();
            cmd.CommandText = @"SELECT 1 FROM mdm.tblModelVersion WHERE [Name] = @version";
            cmd.Parameters.AddWithValue("version", value);
            try
            {
                object o = cmd.ExecuteScalar();
                if (o == null || (int)o != 1) throw new Exception();
            }
            catch
            {
                FireEvent(EventType.Error, "The specified Version does not exist");
                return DTSValidationStatus.VS_ISBROKEN;
            }

            //Property is valid
            return DTSValidationStatus.VS_ISVALID;
        }

        private DTSValidationStatus ValidateEntityProperty()
        {
            //Check for property existence
            IDTSCustomProperty100 prop = ComponentMetaData.CustomPropertyCollection[ENTITY_NAME];

            if (prop == null)
            {
                throw new ArgumentException("Custom property ENTITY_NAME does not exist.");
            }

            //Check whether property value is NULL or empty
            string value = (prop.Value == null) ? null : ((string)prop.Value).Trim();
            if (string.IsNullOrEmpty(value))
            {
                FireEvent(EventType.Error, "The Entity Name property must be set");
                return DTSValidationStatus.VS_ISBROKEN;
            }

            //Check whether property value is invalid
            SqlCommand cmd = state.MdsConnection.CreateCommand();
            cmd.CommandText = @"SELECT 1 FROM mdm.tblEntity WHERE [Name] = @entity";
            cmd.Parameters.AddWithValue("entity", value);
            try
            {
                object o = cmd.ExecuteScalar();
                if (o == null || (int)o != 1) throw new Exception();
            }
            catch
            {
                FireEvent(EventType.Error, "The specified Entity does not exist");
                return DTSValidationStatus.VS_ISBROKEN;
            }

            //Property is valid
            return DTSValidationStatus.VS_ISVALID;
        }

        private DTSValidationStatus ValidateMemberTypeProperty()
        {
            //Check for property existence
            IDTSCustomProperty100 prop = ComponentMetaData.CustomPropertyCollection[MEMBERTYPE_NAME];

            if (prop == null)
            {
                throw new Exception("Custom property MEMBERTYPE_NAME does not exist.");
            }

            //Check whether property value is NULL or empty
            if (prop.Value == null)
            {
                FireEvent(EventType.Error, "The Member Type property must be set");
                return DTSValidationStatus.VS_ISBROKEN;
            }

            //Property is valid
            return DTSValidationStatus.VS_ISVALID;
        }

        private DTSValidationStatus ValidateHierarchyProperty()
        {
            //Check for property existence
            IDTSCustomProperty100 prop = ComponentMetaData.CustomPropertyCollection[HIERARCHY_NAME];

            if (prop == null)
            {
                throw new Exception("Custom property HIERARCHY_NAME does not exist.");
            }

            //Property value is allowed to be NULL or empty

            //Property is valid
            return DTSValidationStatus.VS_ISVALID;
        }

        private DTSValidationStatus ValidateBatchProperty()
        {
            //Check for property existence
            IDTSCustomProperty100 prop = ComponentMetaData.CustomPropertyCollection[BATCH_NAME];

            if (prop == null)
            {
                throw new ArgumentException("Custom property BATCH_NAME does not exist.");
            }

            //Property value is allowed to be NULL or empty

            //Property is valid
            return DTSValidationStatus.VS_ISVALID;
        }

        private DTSValidationStatus ValidateUsernameProperty()
        {
            //Check for property existence
            IDTSCustomProperty100 prop = ComponentMetaData.CustomPropertyCollection[USER_NAME];

            if (prop == null)
            {
                throw new Exception("Custom property USER_NAME does not exist.");
            }

            //Check whether property value is NULL or empty
            string value = (prop.Value == null) ? null : ((string)prop.Value).Trim();
            if (string.IsNullOrEmpty(value))
            {
                FireEvent(EventType.Error, "The Username property must be set");
                return DTSValidationStatus.VS_ISBROKEN;
            }

            //Property is valid
            return DTSValidationStatus.VS_ISVALID;
        }

        private DTSValidationStatus ValidateExternalMetadataInformation()
        {
            DTSValidationStatus status = DTSValidationStatus.VS_ISVALID;
            IDTSInput100 input = ComponentMetaData.InputCollection[0];
            IDTSInputColumnCollection100 icc = input.InputColumnCollection;

            //Check if external metadata columns match with destination database columns
            if (state.IsConnected && ComponentMetaData.ValidateExternalMetadata)
            {
                //status = ValidateWithExternalMetadata();
                if (status != DTSValidationStatus.VS_ISVALID)
                {
                    return status;
                }
            }

            //Check types of every pair of input col and mapped external col
            IDTSExternalMetadataColumnCollection100 ecc = input.ExternalMetadataColumnCollection;
            for (int i = 0; i < icc.Count; i++)
            {
                IDTSInputColumn100 col = icc[i];
                int id = col.ExternalMetadataColumnID;
                IDTSExternalMetadataColumn100 ec;
                try
                {
                    ec = ecc.FindObjectByID(id);
                }
                catch (COMException)
                {
                    //There is no external metadata column for this input column.
                    bool bCancel;
                    ErrorSupport.FireErrorWithArgs(HResults.DTS_E_COLUMNMAPPEDTONONEXISTENTEXTERNALMETADATACOLUMN, out bCancel, col.IdentificationString);
                    return DTSValidationStatus.VS_ISBROKEN;
                }

                CheckTypes(col, ec);
            }

            return status;
        }

        private void CheckTypes(IDTSInputColumn100 ic, IDTSExternalMetadataColumn100 ec)
        {
            //Implicit conversion allowed, but warn if potential truncation
            bool isLong = false;
            DataType dti = ConvertBufferDataTypeToFitManaged(ic.DataType, ref isLong);
            DataType dte = ec.DataType;

            if (dti == dte && (dti == DataType.DT_WSTR || dti == DataType.DT_BYTES))
            {
                if (ic.Length > ec.Length)
                {
                    ErrorSupport.FireWarningWithArgs(HResults.DTS_W_POTENTIALTRUNCATIONFROMDATAINSERTION, ic.Name, ic.Length, ec.Name, ec.Length);
                }
            }

            //Check for numeric types including integers and floating numbers
            if (!IsCompatibleNumericTypes(dti, dte))
            {
                ErrorSupport.FireWarningWithArgs(HResults.DTS_W_ADODESTPOTENTIALDATALOSS, ic.Name, Enum.GetName(typeof(DataType), dti), ec.Name, Enum.GetName(typeof(DataType), dte));
            }
        }

        #endregion

        #region PreExecute

        public override void PreExecute()
        {
            base.PreExecute();

            IDTSCustomPropertyCollection100 cpc = this.ComponentMetaData.CustomPropertyCollection;

            if (!state.IsConnected)
            {
                bool cancel;
                ErrorSupport.FireError(HResults.DTS_E_CONNECTIONREQUIREDFORMETADATA, out cancel);
                throw new PipelineComponentHResultException(HResults.DTS_E_CONNECTIONREQUIREDFORMETADATA);
            }

            if (ValidateModelProperty() != DTSValidationStatus.VS_ISVALID)
                throw new PipelineComponentHResultException(HResults.DTS_E_INCORRECTCUSTOMPROPERTYVALUEFOROBJECT);

            if (ValidateVersionProperty() != DTSValidationStatus.VS_ISVALID)
                throw new PipelineComponentHResultException(HResults.DTS_E_INCORRECTCUSTOMPROPERTYVALUEFOROBJECT);

            if (ValidateEntityProperty() != DTSValidationStatus.VS_ISVALID)
                throw new PipelineComponentHResultException(HResults.DTS_E_INCORRECTCUSTOMPROPERTYVALUEFOROBJECT);

            if (ValidateMemberTypeProperty() != DTSValidationStatus.VS_ISVALID)
                throw new PipelineComponentHResultException(HResults.DTS_E_INCORRECTCUSTOMPROPERTYVALUEFOROBJECT);

            if (ValidateHierarchyProperty() != DTSValidationStatus.VS_ISVALID)
                throw new PipelineComponentHResultException(HResults.DTS_E_INCORRECTCUSTOMPROPERTYVALUEFOROBJECT);

            if (ValidateBatchProperty() != DTSValidationStatus.VS_ISVALID)
                throw new PipelineComponentHResultException(HResults.DTS_E_INCORRECTCUSTOMPROPERTYVALUEFOROBJECT);

            if (ValidateUsernameProperty() != DTSValidationStatus.VS_ISVALID)
                throw new PipelineComponentHResultException(HResults.DTS_E_INCORRECTCUSTOMPROPERTYVALUEFOROBJECT);

            state.Init(cpc, this.ComponentMetaData.InputCollection[INPUT_NAME], this.BufferManager);

            this.CreateBatch();
        }

        private void CreateBatch()
        {
            SqlCommand cmd = state.MdsConnection.CreateCommand();
            cmd.CommandText = @"
                    DECLARE @Version_ID INT, @Return_ID INT, @User_ID INT = 1;

                    SELECT @Version_ID = v.ID 
                    FROM mdm.tblModel AS m 
                    INNER JOIN mdm.tblModelVersion AS v ON (m.ID = v.Model_ID) 
                    WHERE m.[Name] = @model AND v.[Name] = @version;

                    EXEC mdm.udpStagingBatchSave @UserID=@User_ID, @VersionID=@Version_ID, @Name=@batch, @StatusID=0, @ReturnID=@Return_ID OUTPUT;
                    
                    SELECT @Return_ID;";
            cmd.Parameters.AddWithValue("model", state.ModelName);
            cmd.Parameters.AddWithValue("version", state.VersionName);
            cmd.Parameters.AddWithValue("batch", state.BatchName);

            int? batchId = (int?)cmd.ExecuteScalar();
            state.BatchId = batchId;
        }

        #endregion

        #region ProcessInput

        public override void ProcessInput(int inputID, PipelineBuffer buffer)
        {
            base.ProcessInput(inputID, buffer);

            while (buffer.NextRow())
            {
                string code = (string)GetBufferColumnValue(buffer, state.CodeColumnInfo);

                DataRow member = state.Members.NewRow();
                member[state.BatchIdOrdinal] = state.BatchId;
                member[state.UsernameOrdinal] = state.Username;
                member[state.ModelNameOrdinal] = state.ModelName;
                member[state.EntityNameOrdinal] = state.EntityName;
                member[state.MemberTypeIdOrdinal] = (byte)state.MemberType;
                member[state.HierarchyNameOrdinal] = state.HierarchyName;
                member[state.MemberCodeOrdinal] = code;
                member[state.MemberNameOrdinal] = (string)GetBufferColumnValue(buffer, state.NameColumnInfo);
                state.Members.Rows.Add(member);

                foreach (var ci in state.ColumnInfos)
                {
                    object value = GetBufferColumnValue(buffer, ci.Value);

                    DataRow attribute = state.Attributes.NewRow();
                    attribute[state.BatchIdOrdinal] = state.BatchId;
                    member[state.UsernameOrdinal] = state.Username;
                    attribute[state.ModelNameOrdinal] = state.ModelName;
                    attribute[state.EntityNameOrdinal] = state.EntityName;
                    attribute[state.MemberTypeIdOrdinal] = (byte)state.MemberType;
                    attribute[state.MemberCodeOrdinal] = code;
                    attribute[state.AttributeNameOrdinal] = ci.Key;
                    attribute[state.AttributeValueOrdinal] = (null == value) ? null : value.ToString();
                    state.Attributes.Rows.Add(attribute);
                }
            }

            using (SqlBulkCopy bcMember = new SqlBulkCopy(state.MdsConnection))
            {
                bcMember.DestinationTableName = "mdm.tblStgMember";

                bcMember.ColumnMappings.Clear();
                bcMember.ColumnMappings.Add("Batch_ID", "Batch_ID");
                bcMember.ColumnMappings.Add("UserName", "UserName");
                bcMember.ColumnMappings.Add("ModelName", "ModelName");
                bcMember.ColumnMappings.Add("EntityName", "EntityName");
                bcMember.ColumnMappings.Add("MemberType_ID", "MemberType_ID");
                bcMember.ColumnMappings.Add("HierarchyName", "HierarchyName");
                bcMember.ColumnMappings.Add("MemberName", "MemberName");
                bcMember.ColumnMappings.Add("MemberCode", "MemberCode");

                try
                {
                    bcMember.WriteToServer(state.Members);
                    state.Members.Rows.Clear();
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }

            using (SqlBulkCopy bcColumn = new SqlBulkCopy(state.MdsConnection))
            {
                bcColumn.DestinationTableName = "mdm.tblStgMemberAttribute";

                bcColumn.ColumnMappings.Clear();
                bcColumn.ColumnMappings.Add("Batch_ID", "Batch_ID");
                bcColumn.ColumnMappings.Add("UserName", "UserName");
                bcColumn.ColumnMappings.Add("ModelName", "ModelName");
                bcColumn.ColumnMappings.Add("EntityName", "EntityName");
                bcColumn.ColumnMappings.Add("MemberType_ID", "MemberType_ID");
                bcColumn.ColumnMappings.Add("MemberCode", "MemberCode");
                bcColumn.ColumnMappings.Add("AttributeName", "AttributeName");
                bcColumn.ColumnMappings.Add("AttributeValue", "AttributeValue");

                try
                {
                    bcColumn.WriteToServer(state.Attributes);
                    state.Attributes.Rows.Clear();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        private static object GetBufferColumnValue(PipelineBuffer buffer, ColumnInfo col)
        {
            if (buffer.IsNull(col.BufferIndex))
                return null;

            switch (col.ColumnDataType)
            {
                case DataType.DT_BOOL:
                    return buffer.GetBoolean(col.BufferIndex);
                case DataType.DT_BYTES:
                    return buffer.GetBytes(col.BufferIndex);
                case DataType.DT_CY:
                    return buffer.GetDecimal(col.BufferIndex);
                case DataType.DT_DATE:
                    return buffer.GetDateTime(col.BufferIndex);
                case DataType.DT_DBDATE:
                    return buffer.GetDate(col.BufferIndex);
                case DataType.DT_DBTIME:
                    return buffer.GetTime(col.BufferIndex);
                case DataType.DT_DBTIME2:
                    return buffer.GetTime(col.BufferIndex);
                case DataType.DT_DBTIMESTAMP:
                    return buffer.GetDateTime(col.BufferIndex);
                case DataType.DT_DBTIMESTAMP2:
                    return buffer.GetDateTime(col.BufferIndex);
                case DataType.DT_DBTIMESTAMPOFFSET:
                    return buffer.GetDateTimeOffset(col.BufferIndex);
                case DataType.DT_DECIMAL:
                    return buffer.GetDecimal(col.BufferIndex);
                case DataType.DT_FILETIME:
                    return buffer.GetDateTime(col.BufferIndex);
                case DataType.DT_GUID:
                    return buffer.GetGuid(col.BufferIndex);
                case DataType.DT_I1:
                    return buffer.GetSByte(col.BufferIndex);
                case DataType.DT_I2:
                    return buffer.GetInt16(col.BufferIndex);
                case DataType.DT_I4:
                    return buffer.GetInt32(col.BufferIndex);
                case DataType.DT_I8:
                    return buffer.GetInt64(col.BufferIndex);
                case DataType.DT_IMAGE:
                    return buffer.GetBlobData(col.BufferIndex, 0, (int)buffer.GetBlobLength(col.BufferIndex));
                case DataType.DT_NTEXT:
                    return buffer.GetBlobData(col.BufferIndex, 0, (int)buffer.GetBlobLength(col.BufferIndex));
                case DataType.DT_NUMERIC:
                    return buffer.GetDecimal(col.BufferIndex);
                case DataType.DT_R4:
                    return buffer.GetSingle(col.BufferIndex);
                case DataType.DT_R8:
                    return buffer.GetDouble(col.BufferIndex);
                case DataType.DT_STR:
                    return buffer.GetString(col.BufferIndex);
                case DataType.DT_TEXT:
                    return buffer.GetBlobData(col.BufferIndex, 0, (int)buffer.GetBlobLength(col.BufferIndex));
                case DataType.DT_UI1:
                    return buffer.GetByte(col.BufferIndex);
                case DataType.DT_UI2:
                    return buffer.GetUInt16(col.BufferIndex);
                case DataType.DT_UI4:
                    return buffer.GetUInt32(col.BufferIndex);
                case DataType.DT_UI8:
                    return buffer.GetUInt64(col.BufferIndex);
                case DataType.DT_WSTR:
                    return buffer.GetString(col.BufferIndex);
                default:
                    return null;
            }
        }

        #endregion

        #region PostExecute

        public override void PostExecute()
        {
            base.PostExecute();
        }

        #endregion

        #endregion

        #region State

        private struct State
        {
            #region Connectivity

            public SqlConnection MdsConnection;
            public bool IsConnected;

            #endregion

            #region Custom properties

            private string _modelName;
            public string ModelName { get { return _modelName; } }

            private string _versionName;
            public string VersionName { get { return _versionName; } }

            private string _entityName;
            public string EntityName { get { return _entityName; } }

            private MemberType _memberType;
            public MemberType MemberType { get { return _memberType; } }

            private string _hierarchyName;
            public string HierarchyName { get { return _hierarchyName; } }

            private int? _batchId;
            public int? BatchId { get { return _batchId; } set { _batchId = value; } }

            private string _batchName;
            public string BatchName
            {
                get
                {
                    if (string.IsNullOrEmpty(_batchName))
                        return string.Format(CultureInfo.InvariantCulture, "{0}/{1}/{2}/{3} ({4})", ModelName, VersionName, EntityName, MemberType.ToString(), DateTime.Now.ToUniversalTime());
                    else
                        return _batchName.Trim();
                }
            }

            private string _username;
            public string Username { get { return _username; } }

            #endregion

            #region DataTables and DataColumn ordinals

            public DataTable Members, Attributes;

            private int _batchIdOrdinal;
            public int BatchIdOrdinal { get { return _batchIdOrdinal; } }

            private int _usernameOrdinal;
            public int UsernameOrdinal { get { return _usernameOrdinal; } }

            private int _modelNameOrdinal;
            public int ModelNameOrdinal { get { return _modelNameOrdinal; } }

            private int _entityNameOrdinal;
            public int EntityNameOrdinal { get { return _entityNameOrdinal; } }

            private int _memberTypeIdOrdinal;
            public int MemberTypeIdOrdinal { get { return _memberTypeIdOrdinal; } }

            private int _hierarchyNameOrdinal;
            public int HierarchyNameOrdinal { get { return _hierarchyNameOrdinal; } }

            private int _memberCodeOrdinal;
            public int MemberCodeOrdinal { get { return _memberCodeOrdinal; } }

            private int _memberNameOrdinal;
            public int MemberNameOrdinal { get { return _memberNameOrdinal; } }

            private int _attributeNameOrdinal;
            public int AttributeNameOrdinal { get { return _attributeNameOrdinal; } }

            private int _attributeValueOrdinal;
            public int AttributeValueOrdinal { get { return _attributeValueOrdinal; } }

            #endregion

            #region ColumnInfo[] and ColumnInfo pointers

            public SortedList<string, ColumnInfo> ColumnInfos;

            private ColumnInfo _codeColumnInfo;
            public ColumnInfo CodeColumnInfo { get { return _codeColumnInfo; } }

            private ColumnInfo _nameColumnInfo;
            public ColumnInfo NameColumnInfo { get { return _nameColumnInfo; } }

            #endregion

            public void Init(IDTSCustomPropertyCollection100 cpc, IDTSInput100 input, IDTSBufferManager100 bufferManager)
            {
                //Custom properties
                _modelName = (string)cpc[MODEL_NAME].Value;
                _versionName = (string)cpc[VERSION_NAME].Value;
                _entityName = (string)cpc[ENTITY_NAME].Value;
                _memberType = (MemberType)cpc[MEMBERTYPE_NAME].Value;
                _hierarchyName = (string)cpc[HIERARCHY_NAME].Value;
                _batchName = (string)cpc[BATCH_NAME].Value;
                _username = (string)cpc[USER_NAME].Value;

                //DataTables
                Members = new DataTable();
                Members.Locale = CultureInfo.InvariantCulture;

                //Add columns for Member table that it shares with Attribute table (and keep column ordinals for performance)
                _batchIdOrdinal = Members.Columns.Add("Batch_ID", typeof(int)).Ordinal;
                _usernameOrdinal = Members.Columns.Add("UserName", typeof(string)).Ordinal; Members.Columns[_usernameOrdinal].MaxLength = 100;
                _modelNameOrdinal = Members.Columns.Add("ModelName", typeof(string)).Ordinal; Members.Columns[ModelNameOrdinal].MaxLength = 50;
                _entityNameOrdinal = Members.Columns.Add("EntityName", typeof(string)).Ordinal; Members.Columns[EntityNameOrdinal].MaxLength = 50;
                _memberTypeIdOrdinal = Members.Columns.Add("MemberType_ID", typeof(byte)).Ordinal;
                _hierarchyNameOrdinal = Members.Columns.Add("HierarchyName", typeof(string)).Ordinal; Members.Columns[HierarchyNameOrdinal].MaxLength = 50;
                _memberCodeOrdinal = Members.Columns.Add("MemberCode", typeof(string)).Ordinal; Members.Columns[MemberCodeOrdinal].MaxLength = 250;

                //The Attribute table shares the same first several columns with the Member table, so clone it
                Attributes = Members.Clone();

                //Add other columns for Member table (and keep column ordinals for performance)
                _memberNameOrdinal = Members.Columns.Add("MemberName", typeof(string)).Ordinal; Members.Columns[MemberNameOrdinal].MaxLength = 250;

                //Add other columns for Attribute table (and keep column ordinals for performance)
                _attributeNameOrdinal = Attributes.Columns.Add("AttributeName", typeof(string)).Ordinal; Attributes.Columns[AttributeNameOrdinal].MaxLength = 50;
                _attributeValueOrdinal = Attributes.Columns.Add("AttributeValue", typeof(string)).Ordinal; Attributes.Columns[AttributeValueOrdinal].MaxLength = 2000;

                //ColumnInfos
                ColumnInfos = new SortedList<string, ColumnInfo>();
                IDTSExternalMetadataColumnCollection100 ecc = input.ExternalMetadataColumnCollection;
                foreach (IDTSInputColumn100 col in input.InputColumnCollection)
                {
                    int bufferIndex = bufferManager.FindColumnByLineageID(input.Buffer, col.LineageID);
                    ColumnInfo ci = new ColumnInfo(col.Name, col.DataType, bufferIndex, col.Length, col.Precision, col.Scale);

                    IDTSExternalMetadataColumn100 ec = ecc.FindObjectByID(col.ExternalMetadataColumnID);
                    if (ec != null)
                    {
                        if (ec.Name == "Code") _codeColumnInfo = ci;
                        else if (ec.Name == "Name") _nameColumnInfo = ci;
                        else ColumnInfos.Add(ec.Name, ci);
                    }
                }
            }
        }

        private struct ColumnInfo
        {
            public ColumnInfo(string columnName, DataType dataType, int bufferIndex, int length, int precision, int scale)
            {
                _columnName = columnName;
                _dataType = dataType;
                _bufferIndex = bufferIndex;
                _length = length;
                _precision = precision;
                _scale = scale;
            }

            private string _columnName;
            public string ColumnName { get { return _columnName; } }

            private DataType _dataType;
            public DataType ColumnDataType { get { return _dataType; } }

            private int _bufferIndex;
            public int BufferIndex { get { return _bufferIndex; } }

            private int _length;
            public int Length { get { return _length; } }

            private int _precision;
            public int Precision { get { return _precision; } }

            private int _scale;
            public int Scale { get { return _scale; } }
        }

        #endregion

        #region Error handling

        private enum EventType
        {
            Information,
            Warning,
            Error
        }

        private void FireEvent(EventType eventType, string description)
        {
            bool cancel = false;

            switch (eventType)
            {
                case EventType.Information:
                    this.ComponentMetaData.FireInformation(0, this.ComponentMetaData.Name, description, string.Empty, 0, ref cancel);
                    break;
                case EventType.Warning:
                    this.ComponentMetaData.FireWarning(0, this.ComponentMetaData.Name, description, string.Empty, 0);
                    break;
                case EventType.Error: //fall through
                default:
                    this.ComponentMetaData.FireError(0, this.ComponentMetaData.Name, description, string.Empty, 0, out cancel);
                    break;
            }
        }

        #endregion
    }
}